﻿CREATE procedure [dbo].[spReportBrazilProjects]
	@yearPosition int,
	@date datetime
AS

DECLARE @year int
SET @year = YEAR(@date) + @yearPosition

DECLARE @previousYear int
SET @previousYear = @year - 1

DECLARE @query varchar(max) , @query2 varchar(max) 

SET @query = 'declare @lastDatePrevious datetime
select @lastDatePrevious = MAX(ReferenceDate) from BookOfWork' + CONVERT(varchar(4), @previousYear) + '
    
select currentBow.PeriodYear as [Period Year]
, FinancialType.AccountLevel1 as [Account Level 1]
, FinancialType.AccountLevel2 as [Account Level 2]
, FinancialType.AccountLevel3 as [Account Level 3]
, currentBow.InitiativeId as [Initiative Id]
, currentBow.InitiativeName as [Initiative Name]
, currentBow.ProgramId as [Program Id]
, currentBow.ProgramName as [Program Name]
, currentBow.ProjectId as [Project Id]
, currentBow.ProjectName as [Project Name]
, currentBow.ProgramManager as [Program Manager]
, currentBow.ProgramStartDate as [Program Start Date]
, currentBow.ProgramEndDate as [Program End Date]
, currentBow.ProjectStatus as [Project Status]
, currentBow.ProjectStartDate as [Project Start Date]
, currentBow.ProjectEndDate as [Project End Date]
, currentBow.ProjectManager as [Project Manager]
, currentBow.BillingClientGocCode as [Billing Client Goc Code]
, currentBow.Client3Name as [Client 3 Name]
, currentBow.Client4Name as [Client 4 Name]
, currentBow.Client5Name as [Client 5 Name]
, currentBow.Client6Name as [Client 6 Name]
, currentBow.Client7Name as [Client 7 Name]
, currentBow.Client8Name as [Client 8 Name]
, currentBow.Client9Name as [Client 9 Name]
, currentBow.Client10Name as [Client 10 Name]
, currentBow.ClientGocCode as [Client Goc Code]
, ClientGOC.ManagedGeography as [Client Geography Code]
, ClientGOC.ManagedGeographyDescription as [Client Geography Description]
, currentBow.System5Name as [System 5 Name]
, currentBow.System6Name as [System 6 Name]
, currentBow.System7Name as [System 7 Name]
, currentBow.System8Name as [System 8 Name]
, currentBow.System9Name as [System 9 Name]
, currentBow.System10Name as [System 10 Name]
, currentBow.SystemGOCCode as [System Goc Code]
, SystemGOC.ManagedGeography as [System Geography Code]
, SystemGOC.ManagedGeographyDescription as [System Geography Description]
, currentBow.SystemRegion as [System Region]
, currentBow.LOB as [LOB]
, currentBow.RuntheBankBuildtheBank as [Run the Bank Build the Bank]
, currentBow.ProgramProjectLinkIndicator as [Program Project Link Indicator]
, currentBow.WorkTypeFinancials as [Work Type Financials]
, currentBow.InitiativeInvestimentCategory as [Init Invest Category]
, currentBow.ProgramInvestimentCategory as [Prog Invest Category]
, currentBow.ProgramCapitalizedIndicator as [Prog Capind]
, currentBow.ProgramStatus as [Program Status]
, currentBow.ProjectCapitalization as [Project Capitalization]
, currentBow.BowFlag as [Bow Flag]
, currentBow.BowFlag2 as [Bow Flag2]
, currentBow.ProjectType as [Project Type]
, currentBow.ProgramCarryoverIndicator as [Program Carryover Indicator]
, currentBow.TaxonomyName as [Taxonomy Name]
, currentBow.CURate as [Cu Rate]
, currentBow.FirstOfProgramDesc as [First of Program Desc]
, currentBow.LobParent as [Lob Parent]
, currentBow.ProgramTechnologyRollUp as [Program Technology RollUp]
, currentBow.AdjustedFlag as [Adjusted Flag]
, currentBow.January as [January Bow]
, currentBow.February as [February Bow]
, currentBow.March as [March Bow]
, currentBow.April as [April Bow]
, currentBow.May as [May Bow]
, currentBow.June as [June Bow]
, currentBow.July as [July Bow]
, currentBow.August as [August Bow]
, currentBow.September as [September Bow]
, currentBow.October as [October Bow]
, currentBow.November as [November Bow]
, currentBow.December as [December Bow]
, currentBow.YearToDate as [Year To date Bow], currentBow.FullYear as [Full Year Bow] 
, IsNull(case FinancialType.AccountLevel1 When ''Cap'' then 0 else currentBow.January end, 0) as [System January Bow]
, IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.January else currentBow.February end, 0) as [System February Bow]
, IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.February else currentBow.March end, 0) as [System March Bow]
, IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.March else currentBow.April end, 0) as [System April Bow]
, IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.April else currentBow.May end, 0) as [System May Bow]
, IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.May else currentBow.June end, 0) as [System June Bow]
, IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.June else currentBow.July end, 0) as [System July Bow]
, IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.July else currentBow.August end, 0) as [System August Bow]
, IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.August else currentBow.September end, 0) as [System September Bow]
, IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.September else currentBow.October end, 0) as [System October Bow]
, IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.October else currentBow.November end, 0) as [System November Bow]
, IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.November else currentBow.December end, 0) as [System December Bow]
, IsNull(case FinancialType.AccountLevel1 When ''Cap'' then 0 else currentBow.January end, 0) +
	IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.January else currentBow.February end, 0) +
	IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.February else currentBow.March end, 0) +
	IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.March else currentBow.April end, 0) +
    IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.April else currentBow.May end, 0) +
	IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.May else currentBow.June end, 0) +
	IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.June else currentBow.July end, 0) +
	IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.July else currentBow.August end, 0) +
	IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.August else currentBow.September end, 0) +
	IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.September else currentBow.October end, 0) +
    IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.October else currentBow.November end, 0) +
	IsNull(case FinancialType.AccountLevel1 When ''Cap'' then currentBow.November else currentBow.December end, 0) as [System Full Year Bow]
, 0 as [Client January Bow], currentBow.January as [Client February Bow]
, currentBow.February as [Client March Bow], currentBow.March as [Client April Bow]
, currentBow.April as [Client May Bow], currentBow.May as [Client June Bow]
, currentBow.June as [Client July Bow], currentBow.July as [Client August Bow]
, currentBow.August as [Client September Bow], currentBow.September as [Client October Bow]
, currentBow.October as [Client November Bow], currentBow.November as [Client December Bow]
, 0 + currentBow.January + currentBow.February + currentBow.March + currentBow.April + currentBow.May +
	currentBow.June + currentBow.July + currentBow.August + currentBow.September + currentBow.October+ currentBow.November as [Client Full Year Bow]
, Program.ProgramOwner as [Program Business Relationship]
from BookOfWork' + CONVERT(varchar(4), @year) + ' currentBow
INNER JOIN FinancialType on currentBow.FinancialTypeId = FinancialType.Id
left join GOC as ClientGOC on ClientGOC.GocAttributes = currentBow.ClientGocCode 
left join GOC as SystemGOC on SystemGOC.GocAttributes = currentBow.SystemGocCode 
LEFT JOIN Program ON Program.Id = ProgramId
Where (ClientGOC.ManagedGeography = ''BR'' OR SystemGOC.ManagedGeography = ''BR'') 
	AND currentBow.ReferenceDate = ''' + CONVERT(varchar(20), @date) + '''' 
	
SET @query2 = ' UNION 
select '+ CONVERT(varchar(4), @year) +' as [Period Year]
, FinancialType.AccountLevel1 as [Account Level 1]
, FinancialType.AccountLevel2 as [Account Level 2]
, FinancialType.AccountLevel3 as [Account Level 3]
, previousBow.InitiativeId as [Initiative Id]
, previousBow.InitiativeName as [Initiative Name]
, previousBow.ProgramId as [Program Id]
, previousBow.ProgramName as [Program Name]
, previousBow.ProjectId as [Project Id]
, previousBow.ProjectName as [Project Name]
, previousBow.ProgramManager as [Program Manager]
, previousBow.ProgramStartDate as [Program Start Date]
, previousBow.ProgramEndDate as [Program End Date]
, previousBow.ProjectStatus as [Project Status]
, previousBow.ProjectStartDate as [Project Start Date]
, previousBow.ProjectEndDate as [Project End Date]
, previousBow.ProjectManager as [Project Manager]
, previousBow.BillingClientGocCode as [Billing Client Goc Code]
, previousBow.Client3Name as [Client 3 Name]
, previousBow.Client4Name as [Client 4 Name]
, previousBow.Client5Name as [Client 5 Name]
, previousBow.Client6Name as [Client 6 Name]
, previousBow.Client7Name as [Client 7 Name]
, previousBow.Client8Name as [Client 8 Name]
, previousBow.Client9Name as [Client 9 Name]
, previousBow.Client10Name as [Client 10 Name]
, previousBow.ClientGocCode as [Client Goc Code]
, ClientGOC.ManagedGeography as [Client Geography Code]
, ClientGOC.ManagedGeographyDescription as [Client Geography Description]
, previousBow.System5Name as [System 5 Name]
, previousBow.System6Name as [System 6 Name]
, previousBow.System7Name as [System 7 Name]
, previousBow.System8Name as [System 8 Name]
, previousBow.System9Name as [System 9 Name]
, previousBow.System10Name as [System 10 Name]
, previousBow.SystemGOCCode as [System Goc Code]
, SystemGOC.ManagedGeography as [System Geography Code]
, SystemGOC.ManagedGeographyDescription as [System Geography Description]
, previousBow.SystemRegion as [System Region]
, previousBow.LOB as [LOB]
, previousBow.RuntheBankBuildtheBank as [Run the Bank Build the Bank]
, previousBow.ProgramProjectLinkIndicator as [Program Project Link Indicator]
, previousBow.WorkTypeFinancials as [Work Type Financials]
, previousBow.InitiativeInvestimentCategory as [Init Invest Category]
, previousBow.ProgramInvestimentCategory as [Prog Invest Category]
, previousBow.ProgramCapitalizedIndicator as [Prog Capind]
, previousBow.ProgramStatus as [Program Status]
, previousBow.ProjectCapitalization as [Project Capitalization]
, previousBow.BowFlag as [Bow Flag]
, previousBow.BowFlag2 as [Bow Flag2]
, previousBow.ProjectType as [Project Type]
, previousBow.ProgramCarryoverIndicator as [Program Carryover Indicator]
, previousBow.TaxonomyName as [Taxonomy Name]
, previousBow.CURate as [Cu Rate]
, previousBow.FirstOfProgramDesc as [First of Program Desc]
, previousBow.LobParent as [Lob Parent]
, previousBow.ProgramTechnologyRollUp as [Program Technology RollUp]
, previousBow.AdjustedFlag as [Adjusted Flag]
, 0 as [January Bow]
, 0 as [February Bow]
, 0 as [March Bow]
, 0 as [April Bow]
, 0 as [May Bow]
, 0 as [June Bow]
, 0 as [July Bow]
, 0 as [August Bow]
, 0 as [September Bow]
, 0 as [October Bow]
, 0 as [November Bow]
, 0 as [December Bow]
, 0 as [Year To date Bow]
, 0 as [Full Year Bow] 
, case FinancialType.AccountLevel1 When ''Cap'' then ISNULL(previousBow.December, 0) else 0 end as [System January Bow]
, 0 as [System February Bow]
, 0 as [System March Bow]
, 0 as [System April Bow]
, 0 as [System May Bow]
, 0 as [System June Bow]
, 0 as [System July Bow]
, 0 as [System August Bow]
, 0 as [System September Bow]
, 0 as [System October Bow]
, 0 as [System November Bow]
, 0 as [System December Bow]
, case FinancialType.AccountLevel1 When ''Cap'' then ISNULL(previousBow.December, 0) else 0 end as [System Full Year Bow]
, previousBow.December as [Client January Bow], 0 as [Client February Bow]
, 0 as [Client March Bow], 0 as [Client April Bow]
, 0 as [Client May Bow], 0 as [Client June Bow]
, 0 as [Client July Bow], 0 as [Client August Bow]
, 0 as [Client September Bow], 0 as [Client October Bow]
, 0 as [Client November Bow], 0 as [Client December Bow]
, previousBow.December as [Client Full Year Bow]
, Program.ProgramOwner as [Program Business Relationship]
from BookOfWork' + CONVERT(varchar(4), @previousYear) + ' previousBow
INNER JOIN FinancialType on previousBow.FinancialTypeId = FinancialType.Id
left join GOC as ClientGOC on ClientGOC.GocAttributes = previousBow.ClientGocCode 
left join GOC as SystemGOC on SystemGOC.GocAttributes = previousBow.SystemGocCode
LEFT JOIN Program ON Program.Id = ProgramId 
Where (ClientGOC.ManagedGeography = ''BR'' OR SystemGOC.ManagedGeography = ''BR'') 
	AND previousBow.ReferenceDate = @lastDatePrevious'
	
EXEC(@query + ' ' + @query2)